/*
 * Daniel R Padilla
 *
 * Copyright (c) 2008, Daniel R Padilla
 *
 * This copyrighted material is made available to anyone wishing to use, modify,
 * copy, or redistribute it subject to the terms and conditions of the GNU
 * Lesser General Public License, as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
 * for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this distribution; if not, write to:
 * Free Software Foundation, Inc.
 * 51 Franklin Street, Fifth Floor
 * Boston, MA  02110-1301  USA
 */
package org.databasefacade.translator.oracle;


import java.util.ArrayList;

import org.databasefacade.select.Comparison;
import org.databasefacade.select.From;
import org.databasefacade.select.Join;
import org.databasefacade.select.Operator;
import org.databasefacade.select.QueryHints;
import org.databasefacade.select.Where;
import org.databasefacade.table.Table;
import org.databasefacade.table.column.Column;
import org.databasefacade.translator.DefaultAnsiSqlTranslator;
import org.databasefacade.update.Update;


public class OracleTranslator extends DefaultAnsiSqlTranslator
{
	public String createTableSQL(Table table, Column primaryKey)
	{
		String sql = "CREATE TABLE IF NOT EXISTS " + table.getTableName() + " ("
			+ primaryKey.getColumnName() + " "
			+ primaryKey.getDatabaseMetaData().getSqlType() + " AUTO_INCREMENT, PRIMARY KEY("
			+ primaryKey.getColumnName() + ")";
		return sql;
	}
	
	/*
	 * Select translation methods
	 */	
	public String getSelectHintSQL(QueryHints hints)
	{
		return hints.getOracleSelectSQL();
	}

	public QueryHints getQueryHints()
	{
		return new OracleQueryHints();
	}
	
	/*
	 * Join Methods
	 */
	public short getJoinNotationMode()
	{
		return Join.NATIVE_NOTATION;
	}
	
	public String getAnsiJoinExtendedSQL(short joinType, String table, String alias, QueryHints hints)
	{
		String actualAlias = new String(alias);
		String sql = null;
		if ( table.equalsIgnoreCase(actualAlias) ) {
			actualAlias = "";
		}
		if ( joinType == Join.INNER ) {
			sql = "INNER JOIN " + table + " " + actualAlias;
		} else if ( joinType == Join.LEFT_OUTER ) {
			sql = "LEFT OUTER JOIN " + table + " " + actualAlias;
		} else if ( joinType == Join.RIGHT_OUTER ) {
			sql = "RIGHT OUTER JOIN " + table + " " + actualAlias;
		} else if ( joinType == Join.FULL_OUTER ) {
			sql = "FULL OUTER JOIN " + table + " " + actualAlias;
		} else if ( joinType == Join.CROSS ) {
			sql = "CROSS JOIN " + table + " " + actualAlias;
		} else {
			return null;
		}
		return sql;
	}
	
	public String buildNativeFromSQL(From from, Where where)
	{
		// Build the native SQL
		StringBuilder sb = new StringBuilder();
		
		// Return empty string when from clause is empty. Being used for update.
		if ( from.getFromTable() == null ) {  
			return sb.toString();
		}
		
		// Native full outer or cross equivalent are not know 
		// at this time so return ansiSQL if join type found.
		for ( int i = 0; i < from.getNumberOfJoins(); i++ ) {
			Join join = from.getJoin(i);
			if ( join.getJoinType() == Join.FULL_OUTER || join.getJoinType() == Join.CROSS ) {
				return from.getSQL();
			}
		}
		
		// Build nativeSQL if you have gotten to this point
		

		// FROM <table1> <alias1>
		sb.append("FROM " + from.getFromTableName() + " " + from.getFromTableAlias());
		for ( int i = 0; i < from.getNumberOfJoins(); i++ ) {
			Join join = from.getJoin(i);
			sb.append("\n      ," + join.getTableName() + " " + join.getTableAlias());
		}

		return sb.toString();
	}
	
	public String buildNativeWhereSQL(From from, Where where)
	{
		// Build the native SQL
		StringBuilder sb = new StringBuilder();
		
		// Return empty string when from clause is empty. Being used for update.
		if ( from.getFromTable() == null ) {  
			return sb.toString();
		}
		
		// Native full outer or cross equivalent are not know 
		// at this time so return ansiSQL if join type found.
		for ( int i = 0; i < from.getNumberOfJoins(); i++ ) {
			Join join = from.getJoin(i);
			if ( join.getJoinType() == Join.FULL_OUTER || join.getJoinType() == Join.CROSS ) {
				return where.getSQL();
			}
		}
		

		// WHERE [<ansiSQL>] [AND]
		String whereSQL = where.getSQL();
		if ( whereSQL.contains("WHERE") ) {
			sb.append(" " + whereSQL);
			if ( from.getNumberOfJoins() > 0 ) {
				sb.append("\n   AND");
			}
		} else {
			if ( from.getNumberOfJoins() > 0 ) {
				sb.append("\n WHERE");
			}
		}

		// Append Native Join Conditions [Use: (+) for outer joins]
		String prefix = " ";
		for ( int i = 0; i < from.getNumberOfJoins(); i++ ) {
			Join join = from.getJoin(i);
			int jt = join.getJoinType();
			boolean isOuterJoin = true;
			if ( jt == Join.INNER || jt == Join.CROSS ) {
				isOuterJoin = false;
			}
			if ( isOuterJoin ) {
				ArrayList compList = join.on().getAllComparisons();
				for ( int c = 0; c < compList.size(); c++ ) {
					Comparison onc = (Comparison) compList.get(c);
					if ( onc != null ) {
						if ( onc.isColumnOnlyCompare && isOuterJoin ) {
							String oracleJoin = assign(onc.leftSideTable,
								onc.leftSideColumn, onc.rightSideTable, onc.rightSideColumn,
								onc.operation, join.getJoinType());
							onc.setNativeSQL(oracleJoin);
						}
					}
				}

				sb.append(prefix + join.on().getNativeSQL().replaceFirst("ON", "").trim());
			} else {
				sb.append(prefix + join.on().getSQL().replaceFirst("ON", "").trim());
			}
			prefix = "\n   AND ";
		}
		return sb.toString();
	}
	
	public String getUpdateSQL(Update update, From from, Where where) {
		StringBuilder sb = new StringBuilder("");
		sb.append("UPDATE " + buildNativeFromSQL(from, where).replaceFirst("FROM", "").trim());
		sb.append(update.getAssignmentOnlySQL(null));
		sb.append(buildNativeWhereSQL(from,where));
		return sb.toString();
	}
	
	private String assign(Table table1, Column col1, Table table2, Column col2, int op, int join)
	{
		String left = col1.getTableAlias() + "." + col1.getColumnName();
		if ( table1 != null ) {
			left = table1.getTableAlias() + "." + col1.getColumnName();
		}
		String right = col2.getTableAlias() + "." + col2.getColumnName();
		if ( table2 != null ) {
			right = table2.getTableAlias() + "." + col2.getColumnName();
		}
		if ( join == Join.LEFT_OUTER ) {
			right += " (+)";
		} else {
			left += " (+)";
		}
		return "(" + left + " " + Operator.operator(op) + " " + right + ")";
	}
}
